<?php

require_once('connect.php');
ini_set("mysql.trace_mode", "0");

$action = mysqli_real_escape_string($conn, $_REQUEST['action']);

switch($action)
{
  case listTasks:
      $limit = 500;
      $filter_name = mysqli_real_escape_string($conn, $_REQUEST['filter_name']);
      $filter = mysqli_real_escape_string($conn, $_REQUEST['filter']);
      if($filter === 'search'){
        $value = mysqli_real_escape_string($conn, $_REQUEST['value']);
        $sql = "SELECT a.id, name, pclass, value, completed, active
                  FROM `tasks` a LEFT OUTER JOIN `priority` c ON a.priority_id = c.id
                  WHERE LOCATE('".$value."', name) > 0";
              
              
//               "SELECT b.id, b.name, pclass, value, completed, active
//                   FROM `tasks_fulltext` b JOIN `tasks` a ON a.id = b.id
//                   LEFT OUTER JOIN `priority` c ON a.priority_id = c.id
//                   WHERE
//               LOCATE('".$value."', b.name) > 0";
                  
                  
                  
//                   "SELECT b.id, b.name, pclass, value, completed, active,
//                   MATCH (
//                   b.name
//                   )
//                   AGAINST (
//                   '".$value."'
//                   )
//                   AS score
//                   FROM `tasks_fulltext` b JOIN `tasks` a ON a.id = b.id
//                   LEFT OUTER JOIN `priority` c ON a.priority_id = c.id
//                   WHERE
//                   MATCH (
//                   b.name
//                   )
//                   AGAINST (
//                   '".$value."'
//                   )";
                  
//         $result = mysqli_query($conn, $sql);
//         if(!$result)
//         {
//           ajax_error($conn, $sql);
//         }
      }  
      else {
        $completed = $_REQUEST['complete'] ? mysqli_real_escape_string($conn, $_REQUEST['complete']) : '';
        $timeframe = $_REQUEST['timeframe'] ? mysqli_real_escape_string($conn, $_REQUEST['timeframe']) : '';
        $sort_field = $_REQUEST['sort_field'] ? mysqli_real_escape_string($conn, $_REQUEST['sort_field']) : '';
        $sort_direction = $_REQUEST['sort_direction'] ? mysqli_real_escape_string($conn, $_REQUEST['sort_direction']) : '';
        if($timeframe == 2)
        {
          $start = date('Y-m-d').' 00:00:00';
          $end = date('Y-m-d').' 23:59:59';
          $timeframe = " AND `updated` > '".$start."' AND `updated` < '".$end."' ";
        }
        elseif($timeframe == 3)
        {
          $start = date('Y-m-d',mktime(0,0,0,date("m") ,date("d")-1,date("Y"))).' 00:00:00';
          $end = date('Y-m-d',mktime(0,0,0,date("m") ,date("d")-1,date("Y"))).' 23:59:59';
          $timeframe = " AND `updated` > '".$start."' AND `updated` < '".$end."' ";
        }
        else
        {
          $timeframe = '';
        }
        if($sort_field === '' && $sort_direction === ''){
          $order_by_clause = 'ORDER BY `active` DESC, `value` DESC, `name` ASC';
        }
        else {
          $order_by_clause = 'ORDER BY `'.$sort_field.'` '.$sort_direction;
        }
        switch($completed)
        {
          case 1:
            $completed_clause = ' ';
            break;
          case 2:
            $completed_clause = ' AND `completed` = 1 ';
            $order_by_clause = 'ORDER BY `updated` ';
            break;
          case 3:
            $completed_clause = ' AND `completed` = 0 ';
            break;
          default:
            $completed_clause = ' AND `completed` = 0 ';
        }
        if(!is_null($filter) && $filter != '')
        {
          switch($filter) {
            case 'notags':
              $filter_clause = ' b.tag_id IS NULL ';
              break;
            case 'active':
              $filter_clause = " AND `active` = '1'";
              $filter_name = 'Active';
              break;
            default:
              $subtags_array = findSubtagsRecursively($conn, $filter);
              $subtags_array[] = $filter;
              $subtags_string = implode("', '", $subtags_array);
              $filter_clause = " AND b.tag_id IN ('$subtags_string') ";
          }
            
            $sql = "SELECT SQL_CALC_FOUND_ROWS a.id, name, pclass, value, completed, active
                FROM `tasks` a 
                JOIN `tasks_tags` b ON a.id = b.task_id
                LEFT OUTER JOIN `priority` c ON a.priority_id = c.id
                WHERE 1=1
                $filter_clause 
                $completed_clause
                $timeframe
                GROUP BY a.id
                $order_by_clause
                LIMIT $limit";
        }
        else
        { 
          $sql = "SELECT SQL_CALC_FOUND_ROWS a.id, name, pclass, completed, active
                    FROM `tasks` a  
                    LEFT OUTER JOIN `priority` c ON a.priority_id = c.id
                    WHERE 1=1
                    $completed_clause
                    $timeframe
                    GROUP BY a.id
                    $order_by_clause
                    LIMIT $limit";
        }
      }
//echo $sql; exit;
      $result = mysqli_query($conn, $sql);
      $result2 = mysqli_query($conn, "SELECT FOUND_ROWS() fr");
      if(!$result) ajax_error($conn, 'ERROR: ', $sql);
      if(!$result2) ajax_error($conn, 'ERROR: ', $sql); 
      $num_rows = mysqli_fetch_object($result2) or die(mysqli_error($conn));
      $display = $limit < $num_rows->fr ? "$limit / ".$num_rows->fr : $num_rows->fr;
      echo changeTitle("$filter_name ($display)");
      while($obj = mysqli_fetch_object($result)) {
      	echo '<li><a id="task_'.$obj->id.'" class="line '.$obj->pclass.' completed_'.$obj->completed.' active_'.$obj->active.'" href="#">'.$obj->name.'</a></li>';
      }
      break;
  
  case listJobs: 
      $limit = 500;
      $sql = "SELECT SQL_CALC_FOUND_ROWS * 
                  FROM `job_seeking_log`
                  ORDER BY `created` DESC";
      $result = mysqli_query($conn, $sql);
      if(!$result) ajax_error($conn, 'ERROR: ', $sql);
      $result2 = mysqli_query($conn, "SELECT FOUND_ROWS() fr");
      $num_rows = mysqli_fetch_object($result2) or die(mysqli_error($conn));
      $display = $limit < $num_rows->fr ? "$limit / ".$num_rows->fr : $num_rows->fr;
      echo changeTitle("Jobs($display)");
      while($obj = mysqli_fetch_object($result)) {
      	echo '<li><a id="task_'.$obj->id.'" class="line completed_'.$obj->completed.'" href="#">'.$obj->company.'</a></li>';
      }
      break;
      
  case addTag:
    $tag = mysqli_real_escape_string($conn, $_REQUEST['tag']);
    $sql = "INSERT INTO `tags` (`name`) values ('".$tag."') ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    
    $sql = "SELECT `id` 
            FROM `tags`
            WHERE `name` = '".$tag."'";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    $obj = mysqli_fetch_object($result);
    echo $obj->id;
    break;
    
  case saveTask:
    $id = mysqli_real_escape_string($conn, $_REQUEST['id']);
    $name = mysqli_real_escape_string($conn, $_REQUEST['name']);
    $task_tags = explode(',', mysqli_real_escape_string($conn, $_REQUEST['task_tags']));
    $priority = mysqli_real_escape_string($conn, $_REQUEST['priority']);
    $note = mysqli_real_escape_string($conn, $_REQUEST['note']);
    
    $result = mysqli_query($conn, "BEGIN");
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    
    if(!$id)
    {
      $sql = "INSERT INTO `tasks` (`name`, `priority_id`, `note`) values ('".$name."' , '".$priority."', '".$note."') ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)";
      $result = mysqli_query($conn, $sql);
      if(!$result) ajax_error($conn, 'ERROR: ', $sql);
      $last_task_id = mysqli_insert_id($conn);
      
//       $sql = "INSERT INTO `tasks_fulltext` (`id`, `name`) values ('".$last_task_id."' , '".$name."') ON DUPLICATE KEY UPDATE id = ".$last_task_id;
//       $result = mysqli_query($conn, $sql);
//       if(!$result) ajax_error($conn, 'ERROR: ', $sql);
      
      if($task_tags[0]){
        $sql = "DELETE FROM `tasks_tags` WHERE `task_id` = '".$last_task_id."'"; //string file)"
        $result = mysqli_query($conn, $sql);
        if(!$result) ajax_error($conn, 'ERROR: ', $sql);
        foreach($task_tags as $tt){
          if($tt != 'new')
          {
            $sql = "INSERT INTO `tasks_tags` (`task_id`, `tag_id`) values ('".$last_task_id."', '".$tt."')";
            $result = mysqli_query($conn, $sql);
            if(!$result) ajax_error($conn, 'ERROR: ', $sql);
          }
        }
      }
    }
    else 
    {
      $last_task_id = $id;
      $sql = 'UPDATE `tasks` 
                SET  `name` = "'.$name.'", 
                     `priority_id` = "'.$priority.'",
                     `note` = "'.$note.'" 
                WHERE `id` = "'.$id.'"';
      $result = mysqli_query($conn, $sql);
      if(!$result) ajax_error($conn, 'ERROR: ', $sql); 
        
//       $sql = 'UPDATE `tasks_fulltext` 
//                 SET  `name` = "'.$name.'"
//                 WHERE `id` = "'.$id.'"';
//       $result = mysqli_query($conn, $sql);
//       if(!$result) ajax_error($conn, 'ERROR: ', $sql);      
      
      if($task_tags[0]){
        $sql = "DELETE FROM `tasks_tags` WHERE `task_id` = '".$id."'";
        $result = mysqli_query($conn, $sql);
        if(!$result) ajax_error($conn, 'ERROR: ', $sql);
        foreach($task_tags as $tt){
          if($tt != 'new')
          {
            $sql = "INSERT INTO `tasks_tags` (`task_id`, `tag_id`) values ('".$id."', '".$tt."')";
            $result = mysqli_query($conn, $sql);
            if(!$result) ajax_error($conn, 'ERROR: ', $sql);
          }
        }
      }
    }
    $result = mysqli_query($conn, "COMMIT");
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    if(is_null($last_task_id)) {
      header("Status:400"); 
      echo 'Missing task id'; 
      exit; 
    }
    else {
      echo $last_task_id;
    }
    break;
    
  case removeTask:
    $id = mysqli_real_escape_string($conn, $_REQUEST['id']);
    $sql = "DELETE FROM `tasks` WHERE `id` = '".$id."' limit 1";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    
//     $sql = "DELETE FROM `tasks_fulltext` WHERE `id` = '".$id."' limit 1";
//     $result = mysqli_query($conn, $sql);
//     if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    
    echo 'Successful';
    break;
    
  case activateTask:
    $id = mysqli_real_escape_string($conn, $_REQUEST['id']);
    $sql = "UPDATE `tasks` SET `active` = 1 WHERE `id` = '".$id."' limit 1";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    echo 'Successful';
    break;
  
  case deactivateTask:
    $id = mysqli_real_escape_string($conn, $_REQUEST['id']);
    $sql = "UPDATE `tasks` SET `active` = 0 WHERE `id` = '".$id."' limit 1";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    echo 'Successful';
    break;
  
  case completeTask:
    $id = mysqli_real_escape_string($conn, $_REQUEST['id']);
    $sql = "UPDATE `tasks` SET `completed` = 1 WHERE `id` = '".$id."' limit 1";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    echo 'Successful';
    break;
    
  case notCompleteTask:
    $id = mysqli_real_escape_string($conn, $_REQUEST['id']);
    $sql = "UPDATE `tasks` SET `completed` = 0 WHERE `id` = '".$id."' limit 1";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    echo 'Successful';
    break;
    
  case getTaskButtonValues:
    $task_id = mysqli_real_escape_string($conn, $_REQUEST['task_id']);
    $sql = "SELECT * FROM `tasks` WHERE id = '".$task_id."'";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    while($obj = mysqli_fetch_object($result)) {
    	$arr[] = $obj;
    }
    echo json_encode($arr);
    break;
    
  case getAllTags:
    $sql = "SELECT id, name FROM `tags`";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    while($obj = mysqli_fetch_object($result)) {
    	$arr[] = $obj;
    }
    echo json_encode($arr);
    break;
    
  case getAllTags2:
    $tag_id = mysqli_real_escape_string($conn, $_REQUEST['tag_id']);
    $sql = "SELECT id, name FROM `tags`";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    while($obj = mysqli_fetch_object($result)) {
      if($obj->id != $tag_id) {
        echo '<option value="'.$obj->id.'">'.$obj->name.'</option>';
      }
    }
    echo json_encode($arr);
    break;
    
  case addSuperTag:
    $super_tag_id = mysqli_real_escape_string($conn, $_REQUEST['super_tag_id']);
    $this_tag_id = mysqli_real_escape_string($conn, $_REQUEST['this_tag_id']);
    $sql = "INSERT INTO `tags_relationships` (`subtag`, `supertag`) VALUES ('".$this_tag_id."', '".$super_tag_id."')";//" ON DUPLICATE KEY UPDATE count = count + 1";
    $result = mysqli_query($conn, $sql); 
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    break;    
    
  case addSubTag:
    $sub_tag_id = mysqli_real_escape_string($conn, $_REQUEST['sub_tag_id']);
    $this_tag_id = mysqli_real_escape_string($conn, $_REQUEST['this_tag_id']);
    $sql = "INSERT INTO `tags_relationships` (`supertag`, `subtag`) VALUES ('".$this_tag_id."', '".$sub_tag_id."')";//" ON DUPLICATE KEY UPDATE count = count + 1";
    $result = mysqli_query($conn, $sql); 
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    break;    
    
  case getTaskTags:
    $task_id = mysqli_real_escape_string($conn, $_REQUEST['task_id']);
    $sql = "SELECT a.id, name 
                  FROM `tags` a 
                  JOIN `tasks_tags` b ON a.id = b.tag_id
                  WHERE b.task_id = '".$task_id."'";
    $result = mysqli_query($conn, $sql); 
    if(!$result) ajax_error($conn, 'ERROR: ', $sql); 
    while($obj = mysqli_fetch_object($result)) {
    	$arr[] = $obj;
    }
    echo json_encode($arr);
    break;
    
  case getTaskPriority:
    $task_id = mysqli_real_escape_string($conn, $_REQUEST['task_id']);
    $sql = "SELECT priority_id
              FROM `tasks`
              WHERE id = '".$task_id."'";
    $result = mysqli_query($conn, $sql); 
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    $obj = mysqli_fetch_object($result);
    echo $obj->priority_id;
    break;
  
  case getTaskNote:
    $task_id = mysqli_real_escape_string($conn, $_REQUEST['task_id']);
    $sql = "SELECT note
              FROM `tasks`
              WHERE id = '".$task_id."'";
    $result = mysqli_query($conn, $sql); 
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    $obj = mysqli_fetch_object($result);
    echo $obj->note;
    break;
    
  case getPriorities:
    $sql = "SELECT id, label FROM `priority`";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    while($obj = mysqli_fetch_object($result)) {
    	$arr[] = $obj;
    }
    echo json_encode($arr);
    break;
    
  case populateBS:
    setlocale(LC_MONETARY, "en_US");
    $sql = "SELECT id, description, amount 
              FROM `balance_sheet`
              ORDER by description ASC";
    $result = mysqli_query($conn, $sql); 
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    $sum = 0;
    echo '<tbody><tr>
          <th class="bs_header">
            description
          </th>
          <th class="bs_header">
            liabilities
          </th>
          <th class="bs_header">
            assets
          </th>
        </tr></tbody>';
    while($obj = mysqli_fetch_object($result)) {
      $amount = money_format("%n", $obj->amount);
      if($obj->amount > 0)
      {
        echo '<tr><td><span id="description_'.$obj->id.'" class="bs_description">'.$obj->description.'</span></td><td></td><td><span class="bs_amount" id="amount_'.$obj->id.'">'.$amount.'</span></td></tr>';
      }
      else 
      {
        echo '<tr><td><span id="description_'.$obj->id.'"  class="bs_description">'.$obj->description.'</span></td><td><span class="bs_amount negative" id="amount_'.$obj->id.'">'.$amount.'</span></td><td></td><td></tr>';
      }
      $sum = $sum + $obj->amount;
    }
    $class = $sum < 0 ? 'negative' : '';
    echo changeTitle(money_format("%n", $sum));
    break; 
    
  case removeBS:
    $id = mysqli_real_escape_string($conn, $_REQUEST['id']);
    $sql = "DELETE FROM `balance_sheet` WHERE `id` = '".$id."' LIMIT 1"; ;
    $result = mysqli_query($conn, $sql); 
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    break;
  
  case addBe:
    $word = mysqli_real_escape_string($conn, $_REQUEST['word']);
    $sql = "INSERT INTO `be` (`word`) VALUES ('".$word."') ON DUPLICATE KEY UPDATE count = count + 1";
    $result = mysqli_query($conn, $sql); 
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    break; 
    
  case populateBe:
    $sql = "SELECT word FROM `be`
              ORDER BY count DESC";
    $result = mysqli_query($conn, $sql); 
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    while($obj = mysqli_fetch_object($result)) {
      echo '<div class="be_line">'.$obj->word.'</div>';
    }
    break; 
      
  case eipbsd:
    $id = str_replace('description_', '', mysqli_real_escape_string($conn, $_REQUEST['element_id']));
    $update_value = mysqli_real_escape_string($conn, $_REQUEST['update_value']);
    if($id && $update_value)
    {
      $sql = "UPDATE `balance_sheet` 
              SET  `description` = '".$update_value."'
              WHERE id = '".$id."'";
      $result = mysqli_query($conn, $sql);
      if(!$result) ajax_error($conn, 'ERROR: ', $sql);
      echo $update_value;
    }
    else 
    {
      echo "error .. id was $id and update value was $update_value";
    }
    break;   
      
  case eipbsa:
    $id = str_replace('amount_', '', mysqli_real_escape_string($conn, $_REQUEST['element_id']));
    setlocale(LC_MONETARY, "en_US");
    $update_value = str_replace(",", "",str_replace("$", "", mysqli_real_escape_string($conn, $_REQUEST['update_value'])));

    if($id && $update_value)
    {
      $sql = "UPDATE `balance_sheet` 
              SET  `amount` = '".$update_value."'
              WHERE id = '".$id."'";
      $result = mysqli_query($conn, $sql);
      if(!$result) ajax_error($conn, 'ERROR: ', $sql);
      setlocale(LC_MONETARY, "en_US");
      echo money_format("%n", $update_value);
    }
    else 
    {
      echo "error .. id was $id and update value was $update_value";
    }
    break;
      
  case addBSItem:
    $sql = "INSERT INTO `balance_sheet` (`amount`, `description`) VALUES ('0', '')";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    echo 'Successful';
    break;       

// Counters
  case removeCounterItem:
    $id = mysqli_real_escape_string($conn, $_REQUEST['id']);
    $sql = "DELETE FROM `counters` WHERE `id` = '".$id."' LIMIT 1";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    break;     
  
  case priorityUpCounterItem:
    $id = mysqli_real_escape_string($conn, $_REQUEST['id']);
    $sql = "UPDATE `counters` SET `priority_id` = (`priority_id` + 1) % 4 WHERE `id` = '".$id."' LIMIT 1";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    break;     
  
  case priorityDownCounterItem:
    $id = mysqli_real_escape_string($conn, $_REQUEST['id']);
    $sql = "UPDATE `counters` SET `priority_id` = (`priority_id` - 1) % 4 WHERE `id` = '".$id."' LIMIT 1";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    break; 
    
  case addCounter:
    $sql = 'INSERT INTO `counters` (`goal`, `priority_id`) VALUES (100, \'3\');';
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    break;
    
  case resetAllCounters:
    $sql = 'UPDATE `counters` SET VALUE = 0';
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    break;
    
  case populateCounters:
    $sql = "SELECT a.id, a.value val, description, goal, frequency, reward, reward_increment, reward_value, pclass, b.value pval, ADDTIME(updated, '3:2:30:0.000000') updated
              FROM `counters` a
              LEFT OUTER JOIN `priority` b ON a.priority_id = b.id
              ORDER by pval DESC, description ASC";
    $result = mysqli_query($conn, $sql); 
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    echo '<tr class="table_header"><th>Step</th><th colspan="3">Count</th></tr>';
    $total_complete = 0;
    $updated = array('date' => 0, 'id' => 0);
    while($obj = mysqli_fetch_object($result)) {
      if($obj->updated > $updated['date']) { $updated['date'] = $obj->updated;  $updated['id'] = $obj->id; }
      $total_complete += $obj->val;
      echo '<tr id="row_'.$obj->id.'" class="'.$obj->pclass.'">
              <td id="counter_description_'.$obj->id.'" class="row_td_'.$obj->id.' counter_description">'.$obj->description.'</td>
              <td style="text-align:center;"><a href="javascript:void();" class="row_td_'.$obj->id.' decrementor" id="decrement_counter_'.$obj->id.'" onclick="this.style.fontSize=\'40px\';">&darr;</a></td>
              <td id="counter_value_'.$obj->id.'" class="row_td_'.$obj->id.' counter_value" style="text-align:center;">'.$obj->val.'</td>
              <td style="text-align:center;"><a href="javascript:void();" class="row_td_'.$obj->id.' incrementor" id="increment_counter_'.$obj->id.'" onclick="this.style.fontSize=\'40px\';">&uarr;</a></td>
            </tr>';
      $count++;
    }
    echo changeTitle('Monthly ('.round($total_complete / 10, 2).'%)');
    echo '<script type="text/javascript"> (function(){ 
      //$("#row_'.$updated['id'].'").css("opacity", ".6");
      $(".row_td_'.$updated['id'].'").css("color", "white");
      $("#counter_description_'.$updated['id'].'").append("<span style=\'color:white; float:right;\'>Updated '.$updated['date'].'</span>");
    })(); </script>';
    break; 
    
  case eipCounterDescription:
    $element_id = str_replace('counter_description_', '', mysqli_real_escape_string($conn, $_REQUEST['element_id']));
    $update_value = mysqli_real_escape_string($conn, $_REQUEST['update_value']);
    $sql = "UPDATE `counters` 
              SET  `description` = '".$update_value."'
              WHERE id = '".$element_id."'";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    echo $update_value;
    break;
    
  case eipCounterValue:
    $element_id = str_replace('counter_value_', '', mysqli_real_escape_string($conn, $_REQUEST['element_id']));
    $update_value = mysqli_real_escape_string($conn, $_REQUEST['update_value']);
    $sql = "SELECT `value` 
              FROM `counters`
              WHERE id = '".$element_id."'";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    $obj = mysqli_fetch_object($result);
    $old_value = $obj->value;
    $delta = $update_value - $old_value;
    $sql = "UPDATE `counters` 
              SET `value` = '".$update_value."',
              `lifetime_value` = `lifetime_value` + '".$delta."'
              WHERE id = '".$element_id."'";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    echo $update_value;
    break;
          
  case increment:  
    $counter_id = mysqli_real_escape_string($conn, $_REQUEST['counter_id']);
    $sql = "UPDATE `counters` 
              SET  `value` = `value` + 1,  `lifetime_value` = `lifetime_value` + 1,
              `reward_value` = `reward_value` + 1 / `reward_increment`
              WHERE id = '".$counter_id."'";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    echo "success";
    break;
    
  case decrement:  
    $counter_id = mysqli_real_escape_string($conn, $_REQUEST['counter_id']);
    $sql = "UPDATE `counters` 
              SET  `value` = `value` - 1,  `lifetime_value` = `lifetime_value` - 1,
              `reward_value` = `reward_value` - 1 / `reward_increment`
              WHERE id = '".$counter_id."'";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    echo "success";
    break;
 
  case listQnaTags:
    $limit = 500;
    $sql = "SELECT SQL_CALC_FOUND_ROWS tag_id, name 
                FROM `qna` a LEFT JOIN `tags` b
                ON a.tag_id = b.id
                GROUP BY name 
                ORDER BY a.id DESC";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    $result2 = mysqli_query($conn, "SELECT FOUND_ROWS() fr");
    $num_rows = mysqli_fetch_object($result2) or die(mysqli_error($conn));
    $display = $limit < $num_rows->fr ? "$limit / ".$num_rows->fr : $num_rows->fr;
    echo changeTitle("Q&A Tags($display)");
    while($obj = mysqli_fetch_object($result)) {
    	echo '<li><a id="qna_tag_id_'.$obj->tag_id.'" class="qna_tag" href="javascript:void()">'.$obj->name.'</a></li>';
    }
    break;
  
  case displayQna:
    $tag_id = mysqli_real_escape_string($conn, $_REQUEST['tag_id']);
    $limit = 500;
    $sql = "SELECT SQL_CALC_FOUND_ROWS * 
                FROM `qna`
                WHERE `tag_id` = '".$tag_id."'
                ORDER BY id DESC";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    $result2 = mysqli_query($conn, "SELECT FOUND_ROWS() fr");
    $num_rows = mysqli_fetch_object($result2) or die(mysqli_error($conn));
    $display = $limit < $num_rows->fr ? "$limit / ".$num_rows->fr : $num_rows->fr;
    echo changeTitle("Questions($display)");
    $i=1;
    echo '<div class="qna_div">';
    while($obj = mysqli_fetch_object($result)) {
    	echo '<div>
              <a id="qna_question_id_'.$i.'" class="qna qna_question" href="javascript:void()">'.$obj->question.'</a>
            </div>
            <div style="margin:10px;">
              <a id="qna_answer_id_'.$i.'" class="qna qna_answer" href="javascript:void()">'.$obj->answer.'</a>
            </div>';
    	$i++;
    }
    echo '</div>';
    break;
    
  case listTags:
    $limit = 500;
    $sql = "SELECT SQL_CALC_FOUND_ROWS id, name 
                FROM `tags` 
                ORDER BY name ASC";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    $result2 = mysqli_query($conn, "SELECT FOUND_ROWS() fr");
    $num_rows = mysqli_fetch_object($result2) or die(mysqli_error($conn));
    $display = $limit < $num_rows->fr ? "$limit / ".$num_rows->fr : $num_rows->fr;
   // echo changeTitle("Tags($display)");
    while($obj = mysqli_fetch_object($result)) {
    	echo '<li><a id="tag_id_'.$obj->id.'" class="tag" href="javascript:void()">'.$obj->name.'</a></li>';
    }
    break;
 
  case eip_tag_name:
    $element_id = mysqli_real_escape_string($conn, $_REQUEST['id']);
    $update_value = mysqli_real_escape_string($conn, $_REQUEST['update_value']);
    $sql = "UPDATE `tags` 
              SET  `name` = '".$update_value."'
              WHERE id = '".$element_id."'";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    echo $update_value;
    break;
  
  case removeSupertag:
    $supertag_id = mysqli_real_escape_string($conn, $_REQUEST['supertag_id']);
    $sql = "DELETE FROM `tags_relationships` WHERE `id` = '".$supertag_id."' LIMIT 1";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    break; 
  
  case removeSubtag:
    $subtag_id = mysqli_real_escape_string($conn, $_REQUEST['subtag_id']);
    $sql = "DELETE FROM `tags_relationships` WHERE `id` = '".$subtag_id."' LIMIT 1";
    $result = mysqli_query($conn, $sql);
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    break; 
  
  case populateTagSupertags:
    $tag_id = mysqli_real_escape_string($conn, $_REQUEST['tag_id']);
    $sql = "SELECT a.id trid, b.id, b.name
              FROM `tags_relationships` a
              LEFT JOIN `tags` b
              ON a.supertag = b.id
              WHERE subtag = '".$tag_id."'";
    $result = mysqli_query($conn, $sql); 
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    echo '<div><h2>Supertags</h2><ul>';
    while($obj = mysqli_fetch_object($result)) {
      echo '<li id="super_tag_item_'.$obj->trid.'" name="'.$obj->name.'" class="supertag_list_item">'.$obj->name.'<a id="remove_supertag_'.$obj->trid.'" href="javascript:void();" style="color:red;" class="remove_supertag no_under"> &times;</a></li>';
    }
    echo '</ul></div>';
    break;
  
  case populateTagSubtags:
    $tag_id = mysqli_real_escape_string($conn, $_REQUEST['tag_id']);
    $sql = "SELECT a.id trid, b.id, b.name
              FROM `tags_relationships` a
              LEFT JOIN `tags` b
              ON a.subtag = b.id
              WHERE supertag = '".$tag_id."'";
    $result = mysqli_query($conn, $sql); 
    if(!$result) ajax_error($conn, 'ERROR: ', $sql);
    echo '<div><h2>Subtags</h2><ul>';
    while($obj = mysqli_fetch_object($result)) {
      echo '<li id="sub_tag_item_'.$obj->trid.'" name="'.$obj->name.'" class="subtag_list_item">'.$obj->name.'<a id="remove_subtag_'.$obj->trid.'" href="javascript:void();" style="color:red;" class="remove_subtag no_under"> &times;</a></li>';
    }
    echo '</ul></div>';
    break;
       
  case removeTag:
    $id = mysqli_real_escape_string($conn, $_REQUEST['id']);
    $sql = "DELETE FROM `tags` WHERE `id` = '".$id."' LIMIT 1";
    $result = mysqli_query($conn, $sql);
    if(!$result)
    {
      ajax_error($conn, 'ERROR: ', $sql);
    }
    else
    {
      header("Status: 200");
      echo 'Successfully removed tag';
    }
    break; 
 
  case loadHomeShortcuts:
    $sql = "SELECT * FROM `tags`
            WHERE `home_link` = '1'";
    $result = mysqli_query($conn, $sql);
    if(!$result)
    {
      ajax_error($conn, 'ERROR: ', $sql);
    }
    else
    {
      $lines = array();
      $lines['Active'] = '<li><a id="home_active" class="home_shortcut" href="#task_list">Active</a></li>';
      while($obj = mysqli_fetch_object($result)){
        $lines[$obj->name] = '<li><a id="home_'.$obj->id.'" class="home_shortcut" href="#task_list">'.$obj->name.'</a></li>'; 
      }
      ksort($lines);
      foreach($lines as $l){
        echo $l;
      }
    }
    break;
    
  case checkHomeShortcut:
    $tag_id = mysqli_real_escape_string($conn, $_REQUEST['tag_id']);
    $sql = "SELECT count(*) as cnt FROM `tags`
            WHERE `home_link` = '1'
            AND `id` = '".$tag_id."'";
    $result = mysqli_query($conn, $sql);
    if(!$result)
    {
      ajax_error($conn, 'ERROR: ', $sql);
    }
    else
    {
      $obj = mysqli_fetch_object($result);
      echo $obj->cnt;
    }  
    break;
    
  case toggleHomeShortcut:
    $tag_id = mysqli_real_escape_string($conn, $_REQUEST['tag_id']);
    if(mysqli_real_escape_string($conn, $_REQUEST['checked']) === 'true'){
      $checked = 1;
    }
    else {
      $checked = 0;
    }
    $sql = "UPDATE `tags`
            SET `home_link` = '".$checked."'
            WHERE `id` = '".$tag_id."'";
    $result = mysqli_query($conn, $sql);
    if(!$result)
    {
      ajax_error($conn, 'ERROR: ', $sql);
    }
    else
    {
      if($checked){
        echo 'This tag is now a home shortcut';
      }
      else {
        echo 'This tag is now NOT a home shortcut';
      }
    }  
    break;
    
  default:
    ajax_error($conn, 'no action given');
}

function ajax_error($conn, $msg, $sql = ''){
  header("Status: 400 $sql");
  echo $msg;
  if($sql !== ''){
    die(mysqli_error($conn).'  The query was: '.$sql);
  }
}

function changeTitle($display)
{
  return "<script type='text/javascript'>
              (function()
               {
                 $('#Task_list').attr('title', '".$display."');
                 $('#page_title').html('".$display."');
               }
              )();
            </script>";
}

function findSubtagsRecursively($conn, $filter_tag_id, $subtags_array = array(), $i=0)
{
  $sql = "SELECT subtag FROM `tags_relationships` WHERE `supertag` = '".$filter_tag_id."'";
  $result = mysqli_query($conn, $sql);
  if(!$result)die(mysqli_error($conn).'  The query was: '.$sql);
  while($obj = mysqli_fetch_object($result)) {
   	$subtags_array[] = $obj->subtag;
  }
  if($filter_tag_id)
  {
    return findSubtagsRecursively($conn, $subtags_array[$i], $subtags_array, ++$i);
  }
  else 
  {
    return $subtags_array;
  }
}